﻿//*******************************************************************/
//** 编 写 人：lardust-PC AFCodeAI
//** 编写日期：07/08/2013 14:01:36
//** 实现功能：
//** 修 改 人：
//** 修改日期：
//*******************************************************************/
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using CrazyTalk.Common;
using CrazyTalk.Data;
using CrazyTalk.IDAL;
using CrazyTalk.Models;

namespace CrazyTalk.MSSQLDAL
{
	public class TopicUrlMatchDAL: ITopicUrlMatchDAL
	{   		     
		
		/// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(TopicUrlMatch model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TopicUrlMatch(");
            strSql.Append("Url,TopicBaseId)");
            strSql.Append(" values (");
            strSql.Append("@Url,@TopicBaseId)");  
            strSql.Append(";select @@IDENTITY");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@Url", SqlDbType.VarChar,50){ Value = model.Url },
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = model.TopicBaseId }
			};

            return DbHelperSQL.GetInt(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <returns></returns>
        public bool Delete(int urlMatchId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from TopicUrlMatch ");
            strSql.Append(" where UrlMatchId=@UrlMatchId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@UrlMatchId", SqlDbType.Int,4){ Value = urlMatchId }
			};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

		/// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public bool DeleteList(List<int> idList)
        {
            if (idList.Count == 0)
                return false;
            StringBuilder sbWhere = new StringBuilder();
            List<IDataParameter> lsPar = new List<IDataParameter>();
            for (int i = 0; i < idList.Count; i++)
            {
                string parameterName = "@Id" + i;
                sbWhere.AppendFormat("{0},", parameterName);
                lsPar.Add(new SqlParameter(parameterName, idList[i]));
            }
            sbWhere.Remove(sbWhere.Length - 1, 1);
            return DbHelperSQL.ExecuteSql(string.Format("delete from TopicUrlMatch where UrlMatchId in ({0})", sbWhere), lsPar.ToArray()) == idList.Count;
        }

		/// <summary>
        /// 更新
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool Update(TopicUrlMatch model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TopicUrlMatch set Url=@Url,TopicBaseId=@TopicBaseId");            
            strSql.Append(" where  UrlMatchId=@UrlMatchId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@Url", SqlDbType.VarChar,50){ Value = model.Url },
				new SqlParameter("@TopicBaseId", SqlDbType.Int,4){ Value = model.TopicBaseId }
			};

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

		/// <summary>
        /// 是否存在行
        /// </summary>
        /// <returns></returns>
        public bool Exists(int urlMatchId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from TopicUrlMatch where UrlMatchId=@UrlMatchId ");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@UrlMatchId", SqlDbType.Int,4){ Value = urlMatchId }
			};

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

		/// <summary>
        /// 获取数据实体
        /// </summary>
        /// <returns></returns>
        public TopicUrlMatch GetModel(int urlMatchId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from TopicUrlMatch where UrlMatchId=@UrlMatchId");
            IDataParameter[] parameters = 
			{
				new SqlParameter("@UrlMatchId", SqlDbType.Int,4){ Value = urlMatchId }
			};

            DataTable dtb = DbHelperSQL.GetDataTable(strSql.ToString(), parameters);
            if (dtb.Rows.Count > 0)
            {
                return CreateModel(dtb.Rows[0]);
            }
            return null;
        }

		/// <summary>
        /// 使用数据行创建数据实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private TopicUrlMatch CreateModel(DataRow dr)
        {
            TopicUrlMatch model = new TopicUrlMatch();
						model.UrlMatchId = ConvertUtil.ToInt(dr["UrlMatchId"]);
			model.Url = dr["Url"].ToString();
			model.TopicBaseId = ConvertUtil.ToInt(dr["TopicBaseId"]);
            
            return model;
        }

		/// <summary>
        /// 获取记录详情
        /// </summary>
        /// <returns></returns>
        public DataTable GetDetails(int urlMatchId)
        {
			IDataParameter[] parameters = 
			{
				new SqlParameter("@UrlMatchId", SqlDbType.Int,4){ Value = urlMatchId }
			};

            return DbHelperSQL.GetDataTable(@"select * from TopicUrlMatch where UrlMatchId=@UrlMatchId",parameters);
        }

		/// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="queryField">查询条件的字段名称集合，字段格式：a.FieldName,>,参数名附加字符</param>
        /// <param name="queryValue">查询条件的值集合</param>
        /// <param name="orderby">排序列名</param>
        /// <param name="index">页码，从1开始</param>
        /// <param name="size">页记录数</param>
        /// <param name="totalCount">输出的总记录行数</param>
        /// <returns></returns>
        public DataTable GetList(List<string> queryField, List<object> queryValue, string orderby, int index, int size, out int totalCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT TT.* FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.UrlMatchId");
            }
            strSql.Append(")AS Row, T.*  from TopicUrlMatch T where 1=1");
            StringBuilder sbWhere = new StringBuilder();
            List<IDataParameter> parm = new List<IDataParameter>();
            if (queryField != null && queryField.Count > 0)
            {
                for (int i = 0; i < queryField.Count; i++)
                {
                    string qualifier = " = ";
                    string parameterAddin = "";
                    //最后次变量用于存储 判断限定符 左边 的 输入 如 a.FieldName
                    string fieldNameIncom = queryField[i];
                    string[] curArg = fieldNameIncom.Split(',');
                    if (curArg.Length > 1)
                    {
                        fieldNameIncom = curArg[0];
                        qualifier = curArg[1];
                    }
                    if (curArg.Length > 2)
                    {
                        parameterAddin = curArg[2];
                    }
                    string fieldName = fieldNameIncom;
                    int ditIndex = fieldName.IndexOf('.');
                    if (ditIndex > 0)
                    {
                        fieldName = fieldName.Substring(ditIndex + 1);
                    }
                    sbWhere.AppendFormat(" AND {0}{1}@{2}{3} ", fieldNameIncom, qualifier, fieldName, parameterAddin);
                    parm.Add(new SqlParameter("@" + fieldName + parameterAddin, queryValue[i]));
                }
            }
            totalCount = DbHelperSQL.GetInt(@"select count(*) from TopicUrlMatch as a where 1=1" + sbWhere, parm.ToArray());

            strSql.Append(sbWhere);
            strSql.Append(" ) as TT ");
            int pageIndex = (index - 1) * size;
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", pageIndex, pageIndex + size);

            return DbHelperSQL.GetDataTable(strSql.ToString(), parm.ToArray());
        }   
	}
}